<?php
/*
 * @Author: your name
 * @Date: 2022-01-20 16:26:40
 * @LastEditTime: 2022-02-15 14:16:33
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: /nlog/app/Models/crm_model/CrmLinkman.php
 */

namespace App\Models\crm_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CrmLinkman extends Model
{
    use HasFactory;
    static string $tableName = 'crm_linkman';
    
  
   
    public static function crmLinkmanList($division_id,$whereStr,$limitStr){
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, CASE WHEN `crm_linkman`.`division_id` = 0 THEN '全公司' ELSE `crm_division`.`name` END AS `division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH(  CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `crm_division` ON `crm_division`.`id` = `crm_linkman`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`,  `chain`.`division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) as    `division_id`, CASE WHEN `division`.`name` is null  THEN '全公司'   ELSE  `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division`    ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 )   WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id`  LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`,  `chain`.`division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) as    `division_id`, CASE WHEN `division`.`name` is null  THEN '全公司'   ELSE  `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division`    ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 )   WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id`  LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, `chain`.`division_name`, `crm_customer`.`id` as  `crm_customer_id`, `crm_customer`.`company_name` as  `customer_company_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON  FIND_IN_SET( `crm_linkman`.`id` ,`crm_customer`.`linkman_id`) WHERE `crm_linkman`.`delete_time` IS NULL GROUBY `crm_linkman`.`id`  ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        // echo "SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, `chain`.`division_name`, GROUP_CONCAT(`crm_customer`.`id`) AS `crm_customer_id`, GROUP_CONCAT(`crm_customer`.`company_name`) AS `customer_company_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON FIND_IN_SET( `crm_linkman`.`id`, `crm_customer`.`linkman_id` ) WHERE `crm_linkman`.`delete_time` IS NULL GROUP BY `crm_linkman`.`id`   ".$whereStr." ORDER BY `crm_linkman`.`id` DESC ".$limitStr;
        return DB::select( "SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, `chain`.`division_name`, GROUP_CONCAT(`crm_customer`.`id`) AS `crm_customer_id`, GROUP_CONCAT(`crm_customer`.`company_name`) AS `customer_company_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON FIND_IN_SET( `crm_linkman`.`id`, `crm_customer`.`linkman_id` ) WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr."  GROUP BY `crm_linkman`.`id`    ORDER BY `crm_linkman`.`id` DESC ".$limitStr);
        // return DB::select(" SELECT `crm_linkman`.*,`admins`.`nickName`,`admins`.`realName`,CASE WHEN `crm_linkman`.`division_id` = 0 THEN '全公司' ELSE `crm_division`.`name` END as `division_name`  FROM ( SELECT group_concat(`chain` ) as `chain`   FROM `crm_division` WHERE `delete_time` is null AND  FIND_IN_SET(".$division_id.",`chain`) ) as `chain`   JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( `chain`.`chain` ) - LENGTH( REPLACE ( `chain`.`chain`, ',', '' )) + 1 )   INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = substring_index( substring_index( `chain`.`chain`, ',', b.help_topic_id + 1 ), ',' ,- 1 ) LEFT JOIN `crm_division` ON `crm_division`.`id` = `crm_linkman`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id`  WHERE `crm_linkman`.`delete_time` is null ".$whereStr." 	GROUP BY `id`  ORDER BY `id` DESC  ".$limitStr);
    }
    

    public static function crmLinkmanCount($division_id,$whereStr){
    //    echo "SELECT COUNT(*) as `count`  FROM  ( SELECT `id`  FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON FIND_IN_SET( `crm_linkman`.`id`, `crm_customer`.`linkman_id` ) WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr."  GROUP BY `crm_linkman`.`id` )";
        return DB::select("SELECT COUNT(*) AS `count` FROM ( SELECT `crm_linkman`.`id` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON FIND_IN_SET( `crm_linkman`.`id`, `crm_customer`.`linkman_id` ) WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." GROUP BY `crm_linkman`.`id` ) as `computeCount`");
    }

    public static function   linkmanGirdDataList($whereStr,$limitStr = ''){
        return DB::select("SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, CASE WHEN `crm_linkman`.`division_id` = 0 THEN  '全公司'  ELSE  `chain`.`name` END  `division_name`, GROUP_CONCAT( `crm_customer`.`id` ) AS `crm_customer_id`, GROUP_CONCAT( `crm_customer`.`company_name` ) AS `customer_company_name` FROM `crm_division` as `chain`  RIGHT JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` LEFT JOIN `crm_customer` ON FIND_IN_SET( `crm_linkman`.`id`, `crm_customer`.`linkman_id` ) ".$whereStr." GROUP BY `crm_linkman`.`id` ORDER BY `crm_linkman`.`id` DESC ".$limitStr);
    }
   
   
}

